﻿using ASmile.Extend;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Windows.Forms;

namespace ASmile.Win
{
    public class ExcelNPOI
    {
        /// <summary>
        /// 是否为数字
        /// </summary>
        static bool IsNumber(Type type)
        {
            return
                type == typeof(int) ||
                type == typeof(long) ||
                type == typeof(decimal) ||
                type == typeof(double) ||
                type == typeof(float) ||
                type == typeof(long);
        }
        /// <summary>
        /// 获取单元格类型(xls)
        /// </summary>
        static object GetCellValue(ICell cell)
        {
            if (cell == null) return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }

        static IWorkbook CreateXlsWorkbook(FileStream fs)
        {
            if (fs == null) return new HSSFWorkbook();
            return new HSSFWorkbook(fs);
        }

        static IWorkbook CreateXlsxWorkbook(FileStream fs)
        {
            if (fs == null) return new XSSFWorkbook();
            return new XSSFWorkbook(fs);
        }

        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xls)
        /// CreateWorkbook 调用时 FileStream 参数为null，为共用一个方法
        /// </summary>
        static void ToExcel(Func<FileStream, IWorkbook> CreateWorkbook, DataTable dt, string file, string sheetName = "sheet1")
        {
            IWorkbook hssfworkbook = CreateWorkbook(null);
            ISheet sheet = hssfworkbook.CreateSheet(sheetName);
            //表头
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].Caption);
            }
            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j] == null || dt.Rows[i][j] == DBNull.Value) continue;
                    ICell cell = row1.CreateCell(j);
                    if (IsNumber(dt.Columns[j].DataType))
                    {
                        //cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(Convert.ToDouble(dt.Rows[i][j]));
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
            }
            //转为字节数组
            using (MemoryStream stream = new MemoryStream())
            {
                hssfworkbook.Write(stream);
                var buf = stream.ToArray();
                //保存为Excel文件
                using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
            }
        }
        /// <summary>
        ///  excel(2007 Xlsx)指定数据表设置指定DataTable,顺序可以不一致
        /// </summary>
        public static void SetDataTableXlsx(DataTable dt, string excelFile, string sheetName = null)
        {
            SetDataTable(dt, CreateXlsxWorkbook, excelFile, sheetName);
        }
        /// <summary>
        ///  excel(2003 Xls)指定数据表设置指定DataTable,顺序可以不一致
        /// </summary>
        public static void SetDataTableXls(DataTable dt, string excelFile, string sheetName = null)
        {
            SetDataTable(dt, CreateXlsWorkbook, excelFile, sheetName);
        }

        /// <summary>
        /// excel指定数据表设置指定DataTable,顺序可以不一致
        /// </summary>
        static void SetDataTable(DataTable dt, Func<FileStream, IWorkbook> CreateWorkbook, string excelFile, string sheetName)
        {
            using (FileStream fs = new FileStream(excelFile, FileMode.Open, FileAccess.Read))
            {
                IWorkbook hssfworkbook = CreateWorkbook(fs); //new HSSFWorkbook(fs);

                ISheet sheet = null;
                for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
                {
                    var cSheet = hssfworkbook.GetSheetAt(i);
                    if (sheetName.IsEmpty())
                    {
                        sheet = cSheet;
                        break;
                    }
                    if (!cSheet.SheetName.StringEquals(sheetName))
                    {
                        continue;
                    }
                    sheet = cSheet;
                }
                if (sheet == null)
                {
                    return;
                }
                //表头
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                Dictionary<int, string> colDict = new Dictionary<int, string>();
                for (int j = 0; j < header.LastCellNum; j++)
                {
                    var cell = header.GetCell(j);
                    var colName = cell?.ToString();
                    if (colName.IsEmpty()) continue;
                    if (!dt.Columns.Contains(colName)) continue;
                    colDict[j] = colName;
                }

                //数据
                for (int rowIndex = sheet.FirstRowNum + 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (var col in colDict)
                    {
                        var cellVal = GetCellValue(sheet.GetRow(rowIndex).GetCell(col.Key));
                        if (cellVal != null && cellVal.ToString().IsNotEmpty()) //  && cellVal != DBNull.Value
                        {
                            try
                            {
                                dr[col.Value] = cellVal;
                            }
                            catch (Exception ex)
                            {
                                LogHelper.LogObj.Error($"Excel赋值出错，列：{col.Value}，值：{cellVal}", ex);
                            }
                            
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
        }


        /// <summary>
        /// excel文件转换成DataSet
        /// </summary>
        static DataSet ToDataSet(Func<FileStream, IWorkbook> CreateWorkbook, string excelFile, string sheetName, bool onlyFirst = false)
        {
            DataSet ds = new DataSet();
            using (FileStream fs = new FileStream(excelFile, FileMode.Open, FileAccess.Read))
            {
                IWorkbook hssfworkbook = CreateWorkbook(fs); //new HSSFWorkbook(fs);
                for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
                {
                    ISheet sheet = hssfworkbook.GetSheetAt(i);
                    if (sheetName.IsNotEmpty() && !sheet.SheetName.StringEquals(sheetName))
                    {
                        continue;
                    }
                    DataTable dt = new DataTable();
                    dt.TableName = sheet.SheetName;
                    //表头
                    IRow header = sheet.GetRow(sheet.FirstRowNum);
                    List<int> columns = new List<int>();
                    for (int j = 0; j < header.LastCellNum; j++)
                    {
                        //object obj = GetCellValue(header.GetCell(j) as HSSFCell);
                        object obj = GetCellValue(header.GetCell(j));
                        if (obj == null || obj.ToString() == string.Empty)
                        {
                            dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
                            //continue;
                        }
                        else
                            dt.Columns.Add(new DataColumn(obj.ToString()));
                        columns.Add(j);
                    }
                    //数据
                    for (int rowIndex = sheet.FirstRowNum + 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                    {
                        DataRow dr = dt.NewRow();
                        bool hasValue = false;
                        foreach (int colIndex in columns)
                        {
                            //dr[colIndex] = GetCellValue(sheet.GetRow(rowIndex).GetCell(colIndex) as HSSFCell);
                            dr[colIndex] = GetCellValue(sheet.GetRow(rowIndex).GetCell(colIndex));
                            if (dr[colIndex] != null && dr[colIndex].ToString() != string.Empty)
                            {
                                hasValue = true;
                            }
                        }
                        if (hasValue)
                        {
                            dt.Rows.Add(dr);
                        }
                    }
                    ds.Tables.Add(dt);
                    if (onlyFirst) break;
                }
            }
            return ds;
        }

        public static void ToXls(DataTable dt, string file, string sheetName = "sheet1")
        {
            ToExcel(CreateXlsWorkbook, dt, file, sheetName);
        }

        public static void ToXlsx(DataTable dt, string file, string sheetName = "sheet1")
        {
            ToExcel(CreateXlsxWorkbook, dt, file, sheetName);
        }

        public static DataSet XlsToDataSet(string xlsFile)
        {
            return ToDataSet(CreateXlsWorkbook, xlsFile, null);
        }

        public static DataTable XlsToDataTable(string xlsFile, string sheetName = null)
        {
            var ds = ToDataSet(CreateXlsWorkbook, xlsFile, sheetName, true);
            return ds?.Tables[0];
        }

        public static DataSet XlsxToDataSet(string xlsFile)
        {
            return ToDataSet(CreateXlsxWorkbook, xlsFile, null);
        }

        public static DataTable XlsxToDataTable(string xlsFile, string sheetName = null)
        {
            var ds = ToDataSet(CreateXlsxWorkbook, xlsFile, sheetName, true);
            return ds?.Tables[0];
        }

        public static void SaveAsXls(DataTable dt, string file, string sheetName = "sheet1")
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "excel文件|*.xls";
            sfd.FileName = string.Format("{0}.xls", file);
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                for (int i = dt.Columns.Count - 1; i >= 0; i--)
                {
                    if (dt.Columns[i].Caption == dt.Columns[i].ColumnName)
                    {
                        dt.Columns.RemoveAt(i);
                    }
                }
                ToXls(dt, sfd.FileName, sheetName);
            }
        }

        public static void SaveAsXlsx(DataTable dt, string file, string sheetName = "sheet1")
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "excel文件|*.xlsx";
            sfd.FileName = string.Format("{0}.xlsx", file);
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                for (int i = dt.Columns.Count - 1; i >= 0; i--)
                {
                    if (dt.Columns[i].Caption == dt.Columns[i].ColumnName)
                    {
                        dt.Columns.RemoveAt(i);
                    }
                }
                ToXlsx(dt, sfd.FileName, sheetName);
            }
        }

        public static bool SaveAsGridView(DataGridView dgv, string defFileName, string sheetName = "default")
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "excel文件|*.xls";
            sfd.FileName = string.Format("{0}.xls", defFileName);
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                DataTable dt;
                if (dgv.DataSource is DataTable srcTable)
                {
                    dt = srcTable.Copy();
                    foreach (DataGridViewColumn col in dgv.Columns)
                    {
                        var tbCol = dt.Columns[col.DataPropertyName];
                        if (!col.Visible)
                        {
                            dt.Columns.Remove(tbCol);
                            continue;
                        }
                        tbCol.Caption = col.HeaderText;
                    }
                }
                else
                {
                    dt = new DataTable();
                    foreach (DataGridViewColumn item in dgv.Columns)
                    {
                        if (!item.Visible) continue;
                        var col = dt.Columns.Add(item.DataPropertyName);
                        col.Caption = item.HeaderText;
                    }

                    foreach (DataGridViewRow dgvRow in dgv.Rows)
                    {
                        var dr = dt.NewRow();
                        foreach (DataGridViewColumn dgvCol in dgv.Columns)
                        {
                            var val = dgvRow.Cells[dgvCol.Name].Value;
                            if (val is bool bv)
                            {
                                dr[dgvCol.DataPropertyName] = bv ? "是" : "否";
                            }
                            else
                            {
                                dr[dgvCol.DataPropertyName] = val;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
                ToXls(dt, sfd.FileName, sheetName);
                return true;
            }
            return false;
        }
    }
}